/********************* COMMON DATABASE - Web SQL Database *********************/
//  Probably be replaced by a Indexed Web Database since Web SQL Database is
//  already a dead technology (abandoned by W3C).
//
//  Depends: modal.js
//  Depends: bookdb.js

//http://html5doctor.com/introducing-web-sql-databases/

function BookDatabaseWebSql() {
  var shortName = 'Books';
  var version = '1.0';
  var displayName = 'Books Database';
  var maxSize = 65536;
  
  BookDatabase.call(this, openDatabase(shortName, version, displayName, maxSize));
}

// Extend methods
jQuery.extend( BookDatabaseWebSql.prototype, BookDatabase.prototype, {
});

// Override with WebSql implimentations
BookDatabaseWebSql.prototype.allTags = function(callback) {
  var dfret = this.exec('SELECT * FROM tag ORDER BY name;')
    .then(
      function(resultSet) {
        var tags = [];
        for(var i = 0; i < resultSet.rows.length; i++) {
          var row = resultSet.rows.item(i);
          tags.push(row.name);
        }
        callback(tags);
        dfret.resolve();
      },
      function() {
        alert("allTags failed!");
        dfret.reject();
      }
    );
    return dfret.promise();
}

BookDatabaseWebSql.prototype.init = function() {
  // Create tables
  console.log('Creating tables (start)');
  var dfret = this.exec(
        'CREATE TABLE IF NOT EXISTS book (' +
      '   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,' +
      '   uuid text NULL,' +
      '   publisherId INTEGER NULL,' +
      '   rating INTEGER NULL,' +
      '   isbn text NULL,' +
      '   title text NOT NULL,' +
      '   comments text NULL,' +
      '   seriesId INTEGER NULL,' +
      '   seriesIndex INTEGER NULL' +
      '  );')
  .done(
      this.exec('CREATE TABLE IF NOT EXISTS author (' +
      '   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +
      '   name TEXT NOT NULL' +
      '  );'))
  .done(
      this.exec('CREATE TABLE IF NOT EXISTS bookauthor (' +
      '   bookId INTEGER NOT NULL,' +
      '   authorId INTEGER NOT NULL' +
      '  );'))
  .done(
      this.exec('CREATE TABLE IF NOT EXISTS series (' +
      '   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +
      '   name TEXT NOT NULL' +
      '  );'))
  .done(
      this.exec('CREATE TABLE IF NOT EXISTS publisher (' +
      '   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +
      '   name TEXT NOT NULL' +
      '  );'))
  .done(
      this.exec('CREATE TABLE IF NOT EXISTS tag (' +
      '   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +
      '   name TEXT NOT NULL' +
      '  );'))
  .done(
      this.exec('CREATE TABLE IF NOT EXISTS booktag (' +
      '   bookId INTEGER NOT NULL,' +
      '   tagId INTEGER NOT NULL' +
      '  );')
  );
  
  console.log('Creating tables (done)');
   
  return dfret.promise();
} // init

BookDatabaseWebSql.prototype.savebook = function(book) {
  var publisherId = null;
  var seriesId = null;
  var bookId = null;

  console.log('Saving book: ' + book.title);

  // Publisher
  if (book.publisher.length > 0) {
    this._db.transaction(function(transaction) {
      transaction.executeSql(
        'SELECT id FROM publisher WHERE name = ?;',
        [book.publisher],
        function (transaction, resultSet) {
          if (resultSet.rowsAffected && resultSet.rows.length > 0) {
            publisherId = resultSet.rows.item(0).id;
          } else {
            transaction.executeSql(
              'INSERT INTO publisher (name) VALUES(?);',
              [book.publisher],
              function (transaction, resultSet) {
                if (!resultSet.rowsAffected) { return false; }
                publisherId = resultSet.insertId;
              }
            );
          }
        }
      );
    });
  }

  // Series
  if (book.series.length > 0) {
    this._db.transaction(function(transaction) {
      transaction.executeSql(
        'SELECT id FROM series WHERE name = ?;',
        [book.series],
        function (transaction, resultSet) {
          if (resultSet.rowsAffected && resultSet.rows.length > 0) {
            seriesId = resultSet.rows.item(0).id;
          } else {
            transaction.executeSql(
              'INSERT INTO series (name) VALUES(?);',
              [book.series],
              function (transaction, resultSet) {
                if (!resultSet.rowsAffected) { return false; }
                seriesId = resultSet.insertId;
              }
            );
          }
        }
      );
    });
  }

  // Book
  this._db.transaction(function(transaction) {
    transaction.executeSql(
      'INSERT INTO book (uuid, publisherId, rating, isbn, title, comments, seriesId, seriesIndex) VALUES(?, ?, ?, ?, ?, ?, ?, ?);',
      [book.uuid, publisherId, book.rating, book.isbn, book.title, book.comments, seriesId, book.seriesIndex],
      function (transaction, resultSet) {
        if (!resultSet.rowsAffected) { return false; }
        bookId = resultSet.insertId;
      }
    );
  }); // Book

  // Author(s)
  for (var idx in book.authors) {
    var author = book.authors[idx];
    var authorId = null;
    this._db.transaction(function(transaction) {
      transaction.executeSql(
        'SELECT id FROM author WHERE name = ?;',
        [author],
        function (transaction, resultSet) {
          if (resultSet.rowsAffected && resultSet.rows.length > 0) {
            authorId = resultSet.rows.item(0).id;
          } else {
            transaction.executeSql(
              'INSERT INTO author (name) VALUES(?);',
              [author],
              function (transaction, resultSet) {
                if (!resultSet.rowsAffected) { return false; }
                authorId = resultSet.insertId;
              }
            ); // insert author
          }
        }
      ); // select author
    }); // transaction
    if (authorId != null && authorId > 0) {
      this._db.transaction(function(transaction) {
        transaction.executeSql(
          'INSERT INTO bookauthor (bookId, authorId) VALUES(?, ?);',
          [bookId, authorId]
        ); // insert bookauthor
      });
    }
  } // for

  // Tag(s)
  for (var idx in book.tags) {
    var tag = book.tags[idx];
    var tagId = null;
    this._db.transaction(function(transaction) {
      transaction.executeSql(
        'SELECT id FROM tag WHERE name = ?;',
        [tag],
        function (transaction, resultSet) {
          if (resultSet.rowsAffected && resultSet.rows.length > 0) {
            tagId = resultSet.rows.item(0).id;
          } else {
            transaction.executeSql(
              'INSERT INTO tag (name) VALUES(?);',
              [tag],
              function (transaction, resultSet) {
                if (!resultSet.rowsAffected) { return false; }
                tagId = resultSet.insertId;
              }
            ); // insert tag
          }
        }
      ); // select tag
    }); // transaction
    if (tagId != null && tagId > 0) {
      this._db.transaction(function(transaction) {
        transaction.executeSql(
          'INSERT INTO booktag (bookId, tagId) VALUES(?, ?);',
          [bookId, tagId]
        ); // insert booktag
      });
    }
  } // for
}

BookDatabaseWebSql.prototype.clear = function() {
  console.log('Clearing database (start)');
  this._db.transaction(function(transaction) {
    transaction.executeSql('DELETE FROM bookauthor;');
    transaction.executeSql('DELETE FROM booktag;');
    transaction.executeSql('DELETE FROM author;');
    transaction.executeSql('DELETE FROM tag;');
    transaction.executeSql('DELETE FROM book;');
    transaction.executeSql('DELETE FROM publisher;');
    transaction.executeSql('DELETE FROM series;');
    
    transaction.executeSql('DELETE FROM sqlite_sequence WHERE name=\'bookauthor\';');
    transaction.executeSql('DELETE FROM sqlite_sequence WHERE name=\'booktag\';');
    transaction.executeSql('DELETE FROM sqlite_sequence WHERE name=\'author\';');
    transaction.executeSql('DELETE FROM sqlite_sequence WHERE name=\'tag\';');
    transaction.executeSql('DELETE FROM sqlite_sequence WHERE name=\'book\';');
    transaction.executeSql('DELETE FROM sqlite_sequence WHERE name=\'publisher\';');
    transaction.executeSql('DELETE FROM sqlite_sequence WHERE name=\'series\';');
    
    console.log('Clearing database (done)');
  }); // _db.transaction   
}